package com.uncp.comisiondeadmision.seguridad.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.uncp.comisiondeadmision.seguridad.beans.UsuarioSistemaBean;
import com.uncp.comisiondeadmision.util.MySqlDBConn;



public class MySqlUsuarioSistemaDAO implements UsuarioSistemaDAO{

	@Override
	public UsuarioSistemaBean obtenerpk(Integer cod_usu) throws SQLException {
			UsuarioSistemaBean bean = null;
			String sql= "Select * from usuariosistema where=?";
			
			System.out.println("busca : " + sql);
			
			Connection conn = null;
			try {
				conn = new MySqlDBConn().getConnection();
				PreparedStatement pstm = conn.prepareStatement(sql);
				pstm.setInt(1, cod_usu);
				ResultSet rs = pstm.executeQuery();
				
				while (rs.next()) {
					bean = new UsuarioSistemaBean(); 
					bean.setIntCodUsuario(rs.getInt(1));
					bean.setStrId(rs.getString(2));
					bean.setStrPassword(rs.getString(3));
					bean.setStrTipo(rs.getString(4));
					bean.setStrNombres(rs.getString(5));
					bean.setStrApePat(rs.getString(6));
					bean.setStrApePat(rs.getString(7));
					bean.setStrSexo(rs.getString(8));
								
				}
				rs.close();
				pstm.close();
			} catch (SQLException e) {
                e.printStackTrace();
			}finally{
				conn.close();
			}
		return bean;
	}
	
	@Override
	public UsuarioSistemaBean validaUsuarioSistema(String usuario,
			String password) throws SQLException {
		
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		
		try {
			
			MySqlDBConn mysql = new MySqlDBConn();
			conn = mysql.getConnection();
			
			String sql = "select * from usuariosistema where IDINGRESO=? and PASSWORD=?";
			
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, usuario);
			pstm.setString(2, password);
			
			// 3 consume el Resulset
			rs = pstm.executeQuery();
			UsuarioSistemaBean bean = null;
			
			if(rs.next()) {
				bean = new UsuarioSistemaBean();
				bean.setIntCodUsuario(rs.getInt(1));
				bean.setStrId(rs.getString(2));
				bean.setStrPassword(rs.getString(3));
				bean.setStrTipo(rs.getString(4));
				bean.setStrNombres(rs.getString(5));
				bean.setStrApePat(rs.getString(6));
				bean.setStrApeMat(rs.getString(7));
				bean.setStrSexo(rs.getString(8));
				
				return bean;
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if (rs != null)	 rs.close();
			if (pstm != null) pstm.close();
			if (conn != null)conn.close();
		}

		return null;
		
	}

	@Override
	public int insertaUsuario(UsuarioSistemaBean bean) throws SQLException {
		Connection conn = null;
		PreparedStatement pstm= null;
		int insertados = -1;
		try {
				String sql = "insert into usuariosistema values(null,?,?,?,?,?,?,?)";
				System.out.println("se inserto : " + sql);
				conn= new MySqlDBConn().getConnection();
				pstm=conn.prepareStatement(sql);
				
				
				pstm.setString(1,bean.getStrId());
				pstm.setString(2,bean.getStrPassword());
				pstm.setString(3,bean.getStrTipo());
				pstm.setString(4,bean.getStrNombres());
				pstm.setString(5,bean.getStrApePat());
				pstm.setString(6,bean.getStrApeMat());
				pstm.setString(7,bean.getStrSexo());
				
				insertados= pstm.executeUpdate();
				
		} catch (Exception e) {
				System.out.println(e);
		}finally{
			try {
				if (pstm!= null)pstm.close();
			} catch (SQLException e2) {}
			try {
				if (conn != null) conn.close();
			} catch (SQLException e2) {

			}
		}
		return insertados;
	}

	@Override
	public int actualizaUsuario(UsuarioSistemaBean bean) throws SQLException {
		Connection conn = null;
		PreparedStatement pstm = null;
		
		int actualizados = -1;
		
		try {
			String sql = "update usuariosistema " +
			" set idingreso=?, password=?, tipousuario=?, nombres=?, apepat=?, " +
			" apemat=?, sexo=? where codusuario=? ";
			
			System.out.println("Actualiza :" +sql);
			
			conn= new MySqlDBConn().getConnection();
			pstm = conn.prepareStatement(sql);
			
			pstm.setString(1,bean.getStrId());
			pstm.setString(2,bean.getStrPassword());
			pstm.setString(3,bean.getStrTipo());
			pstm.setString(4,bean.getStrNombres());
			pstm.setString(5,bean.getStrApePat());
			pstm.setString(6,bean.getStrApeMat());
			pstm.setString(7,bean.getStrSexo());
			pstm.setInt(8,bean.getIntCodUsuario());
			
			actualizados= pstm.executeUpdate();
						
		} catch (Exception e) {
				System.out.println(e);
		} finally {
			try {
				if (pstm != null)pstm.close();
			} catch (SQLException e1) {}
			try {
				if (conn != null)conn.close();
			} catch (SQLException e) {}
		}
		return actualizados;
		
	}

	@Override
	public int eliminaUsuario(int codigo) throws SQLException {
		Connection conn= null;
		PreparedStatement pstm = null;
		int eliminados = -1;
		String sql= "";
		try {
			sql= "delete from usuariosistema where codusuario = ?";
			System.out.println("Se elimino :" +sql);
			conn= new MySqlDBConn().getConnection();
			pstm= conn.prepareStatement(sql);
			pstm.setInt(1,codigo);
			eliminados=pstm.executeUpdate();	
		} catch (Exception e) {
			System.out.println(e);
		} finally {
			try {
				if (pstm != null)pstm.close();
			} catch (SQLException e1) {}
			try {
				if (conn != null)conn.close();
			} catch (SQLException e) {}
		}
		return eliminados;
	}

	@Override
	public ArrayList<UsuarioSistemaBean> listarTodos() throws SQLException {
		
		Connection conn= null;
		PreparedStatement pstm=null;
		
		ArrayList<UsuarioSistemaBean> lista = new ArrayList<UsuarioSistemaBean>();
		
		try {
			String sql = "select * from usuariosistema";
			conn = new MySqlDBConn().getConnection();
			pstm=conn.prepareStatement(sql);
			ResultSet rs= pstm.executeQuery();
			System.out.println("Listado : "+ sql);
			
			UsuarioSistemaBean bean = null;
			while (rs.next()){
				bean = new UsuarioSistemaBean();
				bean.setIntCodUsuario(rs.getInt("codusuarioo"));
				bean.setStrNombres(rs.getString("nombres"));
				bean.setStrApePat(rs.getString("apepat"));
				bean.setStrApeMat(rs.getString("apemat"));
				bean.setStrSexo(rs.getString("sexo"));
			}
		} catch (Exception e) {
				System.out.println(e);
		}finally{
			try {
				if (pstm != null)pstm.close();
			} catch (SQLException e1) {}
			try {
				if (conn != null)conn.close();
			} catch (SQLException e) {}
		}
	
		return lista;
	}



}
